package com.zan.easyexceldemo.controller;

import com.alibaba.excel.EasyExcel;
import com.zan.easyexceldemo.entity.ChinesePrescriptionInfo;
import com.zan.easyexceldemo.entity.UploadDataReq;
import com.zan.easyexceldemo.listener.PageReadListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @Author Zan
 * @Create 2024/8/26 19:33
 * @ClassName: TestController
 * @Description : TODO 请用一句话描述该类的功能
 */
@RestController
@Slf4j
public class TestController {

    @PostMapping("/uploadData")
    public void uploadData(@RequestBody UploadDataReq uploadDataReq) {
//        System.getProperty("user.dir")  D:\Code\Java\Learn\easyexcel-demo

        String fileName = uploadDataReq.getFileUrl() + "所有的中药处方扩展信息.xlsx";
        String writeFileName = uploadDataReq.getFileUrl() + "所有的中药处方扩展信息_修改后.xlsx";

        List<ChinesePrescriptionInfo> newList = new ArrayList<>();
        EasyExcel.read(fileName, ChinesePrescriptionInfo.class, new PageReadListener<ChinesePrescriptionInfo>(chinesePrescriptionInfoList -> {
            for (ChinesePrescriptionInfo chinesePrescriptionInfo : chinesePrescriptionInfoList) {
                String efficacy = chinesePrescriptionInfo.getEfficacy();
                String indications = chinesePrescriptionInfo.getIndications();
                // 规则一
                Pattern pattern1 = Pattern.compile("【功效】：(.+?)\\s*【适用人群】：(.+)");
                Matcher matcher1 = pattern1.matcher(efficacy);

                // 规则二
                Pattern pattern2 = Pattern.compile("(.*?)\\s*(适用于|用于)\\s*(.+)");
                Matcher matcher2 = pattern2.matcher(efficacy);

                if (matcher1.find()) {
                    // 规则一匹配，去掉标识符
                    efficacy = matcher1.group(1).trim();
                    indications = matcher1.group(2).trim();
                } else if (matcher2.find()) {
                    // 规则二匹配，保留"用于/适用于"
                    efficacy = matcher2.group(1).trim();
                    indications = matcher2.group(2) + matcher2.group(3).trim();  // 保留"适用于/用于"
                }

                log.info("拆分后的数据 {} {}", efficacy, indications);

                // 你可以在这里将解析后的数据存储到数据库或进行其他处理
                chinesePrescriptionInfo.setNewEfficacy(efficacy);
                chinesePrescriptionInfo.setNewIndications(indications);
                newList.add(chinesePrescriptionInfo);
            }
        })).sheet().doRead();

        // 写入新的excel
        EasyExcel.write(writeFileName, ChinesePrescriptionInfo.class).sheet("模板").doWrite(newList);

//        importToDatabase(newList, uploadDataReq);
    }

    private void importToDatabase(List<ChinesePrescriptionInfo> newList, UploadDataReq uploadDataReq) {
        String updateSQL = "UPDATE chinese_prescription_info SET efficacy = ?, indications = ? WHERE id = ?";

//        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mytest", "root", "root")) {
        try (Connection conn = DriverManager.getConnection(
                uploadDataReq.getDatabaseUrl(),
                uploadDataReq.getUsername(),
                uploadDataReq.getPassword())) {
            conn.setAutoCommit(false);
            try (PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {
                for (ChinesePrescriptionInfo info : newList) {
                    pstmt.setString(1, info.getNewEfficacy() != null ? info.getNewEfficacy() : "");
                    pstmt.setString(2, info.getNewIndications() != null ? info.getNewIndications() : "");
                    pstmt.setLong(3, info.getId());  // 根据id更新
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                conn.commit();
                log.info("数据成功更新至数据库！");
            } catch (SQLException e) {
                conn.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
